#encoding=utf-8
import xadmin,ujson,time,datetime,traceback
#新页面不是model对应生成的，菜单url配置中的地址对应register_view的
from xadmin.views import BaseAdminPlugin, ModelFormAdminView, ModelAdminView, CommAdminView, csrf_protect_m
from xadmin.util import unquote
from django.utils.html import escape
from django import forms
from xadmin.layout import Fieldset, Main, Side, Row, FormHelper
from django.template.response import TemplateResponse
from django.utils.translation import ugettext as _
from django.utils.decorators import method_decorator
from django.http import HttpResponseRedirect
from django.views.decorators.debug import sensitive_post_parameters
from django.conf.urls import patterns, include, url
from django.views.decorators.csrf import csrf_exempt
from django.contrib.auth.decorators import login_required
from django.shortcuts import render_to_response, HttpResponse, HttpResponseRedirect
from django.db.models import Count, Max, F, Q,Min,Sum
import websys.webconfig as webconfig
import websys.fun as fun
from websys.models import (
    AgentInfo,CommissionReport,TableInfo,GameInfo,UserInfo,PlatformInfo,AgentTableReport,Agent_Profit,Detaile_Profit,
IncrInfo
)
from  websys import (
    db_redis
)
from websys.view import recharge

class TestForm(forms.Form):
    """
    A form used to change the password of a user in the admin interface.
    """
    # error_messages = {
    #     'password_mismatch': _("The two password fields didn't match."),
    # }
    # password1 = forms.CharField(label=_("Password"),
    #                             widget=forms.PasswordInput)
    # password2 = forms.CharField(label=_("Password (again)"),
    #                             widget=forms.PasswordInput)

    def __init__(self,  *args, **kwargs):
        #self.user = user
        super(TestForm, self).__init__(*args, **kwargs)

#缓存平台列表
global PFINFO_LIST
PFINFO_LIST=[]#[{'pf_id':'nhmj123','name':'','games':'1,2,3'},...]
def getPlatformInfo_list():
    global PFINFO_LIST
    if not PFINFO_LIST:
        PFINFO_LIST=PlatformInfo.objects.all().values('pf_id',"name","games")
getPlatformInfo_list()
#根据平台id获取游戏列表
def getPF_GAMES(_id):
    if _id=='':
        return ''
    global PFINFO_LIST
    for ii_item in PFINFO_LIST:
        if _id==ii_item['pf_id']:
            return ii_item['games']
    return '0'

#充值报表页面
class RechargeReportView(ModelAdminView):
    model = AgentInfo
    this_form = TestForm
    change_user_password_template = None

    @csrf_protect_m
    def get(self, request):
        if not self.has_change_permission(request):
            raise 123#PermissionDenied
        self.obj = {}
        self.form = self.this_form()

        #同步收入
        recharge.op_sync_commission(request)
        #页面需要的数据,平台列表
        #app_list=PlatformInfo.objects.all().values('pf_id',"name")
        global PFINFO_LIST
        app_list=PFINFO_LIST
        self.htmldata={'apps':app_list}

        return self.get_response()

    def get_media(self):
        media = super(RechargeReportView, self).get_media()
        media = media + self.vendor('xadmin.form.css','datepicker.css','xadmin.plugins.css',
                                    'xadmin.page.form.js','xadmin.widget.datetime.js') + self.form.media
        return media

    def get_context(self):
        #获取数据
        context = super(RechargeReportView, self).get_context()
        #print(context,'-------------------*****get_context*****',)
        helper = FormHelper()
        helper.form_tag = False
        self.form.helper = helper
        context.update({
            #'title': _('Change password: %s') % escape(unicode(self.obj)),
            'form': self.form,
            'has_delete_permission': False,
            'has_change_permission': True,
            'has_view_permission': True,
            'original': self.obj,
            #chong add
            'title':'充值报表',#_('My Information'),
            'account_view':True,
            'serverval':"服务器上的值",
            'htmldata':self.htmldata
        })
        return context

    #可以按不同的参数加载对应的页面
    def get_response(self):
        #print(self.get_context(),"----******get_response*******",self.admin_site.name)
        return TemplateResponse(self.request, ['rechargereport.html'] , self.get_context(), current_app=self.admin_site.name)
        #return TemplateResponse(self.request, [ self.change_user_password_template or'xadmin/auth/user/change_password.html']
                               # , self.get_context(), current_app=self.admin_site.name)

    @method_decorator(sensitive_post_parameters())
    @csrf_protect_m
    def post(self, request, object_id):
        if not self.has_change_permission(request):
            raise 23456
        self.obj = self.get_object(unquote(object_id))
        self.form = self.change_password_form(self.obj, request.POST)

        if self.form.is_valid():
            self.form.save()
            self.message_user(_('Password changed successfully.'), 'success')
            return HttpResponseRedirect(self.model_admin_url('change', self.obj.pk))
        else:
            return self.get_response()

#金钻消耗统计页面
class CommonReportView(ModelAdminView):
    model = AgentInfo
    this_form = TestForm
    common_obj={'jstype':'vendor','css_js':('xadmin.form.css','datepicker.css','xadmin.plugins.css', 'xadmin.page.form.js','xadmin.widget.datetime.js')}
    html_obj={'online':{'title':'在线统计','jstype':'static','css_js':('css/jquery-ui.min.css', 'js/jquery-ui.min.js')},
              'detaile_profit':{'title':'提成明细'},
              'my_detaile_profit':{'title':'我的提成明细'},
              'sytem_data':{'title':'游戏数据统计'},
              'share':{'title':'分享统计','isTemplate':0},#是否用模板
              'table':{'title':'开桌统计','isTemplate':1}
              }
    html_obj['detaile_profit'].update(common_obj)
    html_obj['my_detaile_profit'].update(common_obj)
    html_obj['sytem_data'].update(common_obj)
    html_obj['share'].update(common_obj)
    html_obj['table'].update(common_obj)

    # def __init__(self,request):
    #     _title,_tmpurl='金钻消耗统计','diamondreport.html'
    #     self.title=_title
    #     self.tempurl=_tmpurl
    #     print(_title,_tmpurl)

    @csrf_protect_m
    def get(self, request,_type):
        if not self.has_change_permission(request):
            raise 123#PermissionDenied
        self.obj = {}
        self.form = self.this_form()

        self.htmlconfig=self.html_obj[_type]
        _title,_tmpurl=self.htmlconfig['title'],_type+'report.html'
        if 'isTemplate' in self.htmlconfig and self.htmlconfig['isTemplate']==1:
            _tmpurl='commonreport.html'
        self.title=_title

        self.tempurl=_tmpurl
        global PFINFO_LIST
        self.htmldata={'type':_type,'apps':PFINFO_LIST}

        if(_type=='online'):#游戏列表
            gis=GameInfo.objects.all().values("id","gamename")
            self.htmldata=gis

        return self.get_response()

    def get_media(self):
        media = super(CommonReportView, self).get_media()
        if self.htmlconfig['jstype']=='static':
            for ii in self.htmlconfig['css_js']:
                file_type = ii.split('.')[-1]
                if file_type=='css':
                    media.add_css({'screen': [ii]})
                elif file_type=='js':
                    media.add_js((self.static(ii),))
            #tmp_media=self.static(*self.htmlconfig['css_js'])
        else:
            media+=self.vendor(*self.htmlconfig['css_js'])
        media = media+ self.form.media
        return media

    def get_context(self):
        #获取数据
        context = super(CommonReportView, self).get_context()
        #print(context,'-------------------*****get_context*****',)
        helper = FormHelper()
        helper.form_tag = False
        self.form.helper = helper
        context.update({
            #'title': _('Change password: %s') % escape(unicode(self.obj)),
            'form': self.form,
            'has_delete_permission': False,
            'has_change_permission': True,
            'has_view_permission': True,
            'original': self.obj,
            #chong add
            'title':self.title,#_('My Information'),
            'account_view':True,
            'serverval':"服务器上的值",
            'htmldata':self.htmldata
        })
        return context

    #可以按不同的参数加载对应的页面
    def get_response(self):
        #print(self.get_context(),"----******get_response*******",self.admin_site.name)
        return TemplateResponse(self.request, [self.tempurl] , self.get_context(), current_app=self.admin_site.name)
        #return TemplateResponse(self.request, [ self.change_user_password_template or'xadmin/auth/user/change_password.html']
                               # , self.get_context(), current_app=self.admin_site.name)

class DiamondReportView(CommonReportView):
    def __init__(self):
        super(DiamondReportView,self)#.__init__('金钻消耗统计','diamondreport.html')

class OnlineReportView(CommonReportView):
    def __init__(self):
        super(OnlineReportView,self)#.__init__('在线统计','onlinereport.html')

# 代理局数统计
class AgentGatherReportView(ModelAdminView):
        model = AgentInfo
        this_form = TestForm
        change_user_password_template = None

        @csrf_protect_m
        def get(self, request):
            if not self.has_change_permission(request):
                raise 123  # PermissionDenied
            self.obj = {}
            self.form = self.this_form()

            global PFINFO_LIST
            self.htmldata={'apps':PFINFO_LIST}

            return self.get_response()

        def get_media(self):
            media = super(AgentGatherReportView, self).get_media()
            media = media + self.vendor('xadmin.form.css', 'datepicker.css', 'xadmin.plugins.css',
                                        'xadmin.page.form.js', 'xadmin.widget.datetime.js') + self.form.media
            return media

        def get_context(self):
            # 获取数据
            context = super(AgentGatherReportView, self).get_context()
            # print(context,'-------------------*****get_context*****',)
            helper = FormHelper()
            helper.form_tag = False
            self.form.helper = helper
            context.update({
                # 'title': _('Change password: %s') % escape(unicode(self.obj)),
                'form': self.form,
                'has_delete_permission': False,
                'has_change_permission': True,
                'has_view_permission': True,
                'original': self.obj,
                # chong add
                'title': '代理提成统计',  # _('My Information'),
                'account_view': True,
                'serverval': "服务器上的值",
                'htmldata':self.htmldata,
            })
            return context

        # 可以按不同的参数加载对应的页面
        def get_response(self):
            if self.user.is_superuser:
                html = 'agentprofitreport.html'
            else:
                html = 'myprofitreport.html'
            return TemplateResponse(self.request, [html], self.get_context(), current_app=self.admin_site.name)

        @method_decorator(sensitive_post_parameters())
        @csrf_protect_m
        def post(self, request, object_id):
            if not self.has_change_permission(request):
                raise 23456
            self.obj = self.get_object(unquote(object_id))
            self.form = self.change_password_form(self.obj, request.POST)

            if self.form.is_valid():
                self.form.save()
                self.message_user(_('Password changed successfully.'), 'success')
                return HttpResponseRedirect(self.model_admin_url('change', self.obj.pk))
            else:
                return self.get_response()


#渲染注册请求页面
xadmin.site.register_view(r'^agent/report/$', RechargeReportView,name='agent_report')
xadmin.site.register_view(r'^agent_profit/profit/$', AgentGatherReportView,name='agent_profit_report')
#xadmin.site.register_view(r'^report/diamond/consume/$', DiamondReportView,name='diamond_consume')
xadmin.site.register_view(r'^gamereport/(.+)/$', CommonReportView,name='user_%s')#各种按日，月，年汇总页面

#ajax请求页面
urlpatterns = patterns('',
                    #充值报表
                    url(r'^recharge', 'websys.view.report.op_recharge', name='op_recharge'),
                #钻石消耗统计
                    url(r'^diamondconsume', 'websys.view.report.op_consume', name='op_consume'),
                       #分享统计
                    url(r'^sharecount', 'websys.view.report.op_share', name='op_share'),
                       #开桌统计
                    url(r'^tablecount', 'websys.view.report.op_table', name='op_table'),
                       #在线统计
                    url(r'^onlinedata', 'websys.view.report.op_online', name='op_online'),
                       url(r'^onlinecount', 'websys.view.report.op_onlinecount', name='op_onlinecount'),
                       url(r'^onlinelist', 'websys.view.report.op_onlinelist', name='op_onlinelist'),
                    #代理局数统计(admin)
                    url(r'^gather', 'websys.view.report.admin_gather', name='admin_gather'),
                    #代理局数统计(user)
                    url(r'^my_gathers', 'websys.view.report.my_gather', name='my_gather'),
                    url(r'^detaile_profits', 'websys.view.report.detaile_profit', name='detaile_profit'),
                    url(r'^myprofit', 'websys.view.report.get_myprofit', name='get_myprofit'),
                    url(r'^mydetaile_profits', 'websys.view.report.mydetaile_profit', name='mydetaile_profit'),
                    url(r'^sytem_data', 'websys.view.report.sytem_data', name='sytem_data'),

    )



@csrf_exempt
@login_required(login_url="/xadmin/")
def sytem_data(request):
    ret = []

    _date = request.GET["date"]
    _date_0,_date_1=_date.split(',')
    t_date_start = time.mktime(time.strptime(str(_date_0 + ' 00:00:00'), '%Y-%m-%d %H:%M:%S'))
    t_date_end = time.mktime(time.strptime(str(_date_1 + ' 00:00:00'), '%Y-%m-%d %H:%M:%S'))
    b_date = t_date_start
    while b_date <= t_date_end:
        time_local = time.localtime(b_date)
        dt = time.strftime("%Y-%m-%d %H:%M:%S", time_local)

        dts = datetime.datetime.fromtimestamp(b_date)
        delta = datetime.timedelta(days=1)

        changed_time = dts + delta
        table_info =TableInfo.objects.filter(create_time__gte=dt,create_time__lt=changed_time)
        god_money_info=IncrInfo.objects.filter(remark='QIFU',create_time__gte=dt,create_time__lt=changed_time)
        number=0
        master_fee=0
        table_fee=0
        profit_fee=0
        god_money=0
        for i in table_info:
            master_fee +=i.master_fee
            table_fee +=i.table_fee
            profit_fee +=i.profit_fee
            number +=i.master_fee+i.table_fee+i.profit_fee
        for j in god_money_info:
            god_money +=abs(j.amount)
        data_dic={}
        data_dic['date']=str(dt).split(' ')[0]
        data_dic['value']=number+god_money
        data_dic['master_fee']=master_fee
        data_dic['table_fee']=table_fee
        data_dic['profit_fee']=profit_fee
        data_dic['god_money'] = god_money
        ret.append(data_dic)
        Unixtime = time.mktime(time.strptime(changed_time.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S'))
        b_date = Unixtime
    ret.sort(key=lambda x: x["date"],reverse=True)
    print ret,'---------'
    return HttpResponse(ujson.dumps(ret))



@csrf_exempt
@login_required(login_url="/xadmin/")
def mydetaile_profit(request):
    date=request.GET['date']
    account = request.GET['name']
    if not account:
        account=request.user.username
    date0,date1=str(date).split(',')
    dt0=date0+' 00:00:00'
    dt1=date1+' 23:59:00'
    detaile_info=Detaile_Profit.objects.filter(agent_account=str(account),create_time__gte=dt0,create_time__lt=dt1).order_by('-id')
    lists=[]
    for i in detaile_info:
        profit_dic={}
        profit_dic['account']=i.account
        profit_dic['agent_account']=i.agent_account
        profit_dic['agent_profit']=i.agent_profit
        profit_dic['issue']=i.issue
        profit_dic['create_time']=str(i.create_time)
        lists.append(profit_dic)

    return HttpResponse(ujson.dumps(lists))





@csrf_exempt
@login_required(login_url="/xadmin/")
def get_myprofit(request):
    ret = []
    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码
    username = request.user.username

    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    # 开始日期小于结束日期
    #print _date_0,_date_1
    num_0 = int(_date_0.replace('-', ''))
    num_1 = int(_date_1.replace('-', ''))
    if (num_0 <= num_1):
        # 按年2016  2017
        if act == 0:
            year_start = int(_date_0)  # .split('-')[0])
            year_end = int(_date_1)  # .split('-')[0])
            # _total = year_end - year_start + 1
            year_cur = year_end
            tmp_0 = str(year_start) + '-01-01'
            tmp_1 = str(year_cur) + '-12-31'
            cur_filter=Agent_Profit.objects.filter(date__range=(tmp_0,tmp_1))
            if username!='':
                agentid=AgentInfo.objects.get(account=username).id
                cur_filter=cur_filter.filter(agentid_id=agentid)
            data=cur_filter.values('account').annotate(profits=Sum('profit'))
            for i in data:
                cur_obj = {'account': '', 'profit': 0,'date':''}
                cur_obj['account']=i['account']
                cur_obj['profit']=i['profits']
                cur_obj['date']=str(tmp_0) + ','+str(tmp_1)
                ret.append(cur_obj)
        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            num_cur=num_1
            year_cur=year_start
            tmp_0=year_cur+'-'+month_start+'-01'
            temps = year_end+'-'+month_end+'-01'
            #下个月1号,减少一天
            cur_time=datetime.datetime.strptime(temps, "%Y-%m-%d")
            cur_time=fun.datetime_offset_by_month(cur_time)
            delta=datetime.timedelta(days=-1)
            time_next=cur_time+delta
            tmp_1=time_next.strftime('%Y-%m-%d')
            cur_filter=Agent_Profit.objects.filter(date__range=(tmp_0,tmp_1))
            if username!='':
                agentid=AgentInfo.objects.get(account=username).id
                cur_filter=cur_filter.filter(agentid_id=agentid)
            data=cur_filter.values('account').annotate(profits=Sum('profit'))
            for i in data:
                cur_obj = {'account': '', 'profit': 0,'date':''}
                cur_obj['account']=i['account']
                cur_obj['profit']=i['profits']
                cur_obj['date']=str(tmp_0) + ','+str(tmp_1)
                ret.append(cur_obj)
        elif act == 2:  # 日期
            _y_0, _m_0, _d_0 = _date_0.split('-')
            _y_1, _m_1, _d_1 = _date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0), int(_d_0))
            d1 = datetime.datetime(int(_y_1), int(_m_1), int(_d_1))
            # _total = (d1 - d0).days + 1
            # d_cur = d1 + datetime.timedelta(-_start_index)
            # tmp_0 = d_cur.strftime('%Y-%m-%d')
            # d_next = d_cur + datetime.timedelta(+1)
            # tmp_1 = d_next.strftime('%Y-%m-%d')
            cur_filter=Agent_Profit.objects.filter(date__range=(d0,d1))
            if username!='':
                agentid=AgentInfo.objects.get(account=username).id
                cur_filter=cur_filter.filter(agentid_id=agentid)
            data=cur_filter.values('account').annotate(profits=Sum('profit'))
            for i in data:
                cur_obj = {'account': '', 'profit': 0,'date':''}
                cur_obj['account']=i['account']
                cur_obj['profit']=i['profits']
                cur_obj['date']=str(d0).split(' ')[0] + ','+str(d1).split(' ')[0]
                ret.append(cur_obj)
    #ret = {'total':_total,'rows': ret}
    return HttpResponse(ujson.dumps(ret))






@csrf_exempt
@login_required(login_url="/xadmin/")
def detaile_profit(request):
    date=request.GET['date']
    account = request.GET['name']
    date0,date1=str(date).split(',')
    dt0=date0+' 00:00:00'
    dt1=date1+' 23:59:00'
    detaile_info=Detaile_Profit.objects.filter(agent_account=str(account),create_time__gte=dt0,create_time__lt=dt1).order_by('-id')
    lists=[]
    for i in detaile_info:
        profit_dic={}
        profit_dic['account']=i.account
        profit_dic['agent_account']=i.agent_account
        profit_dic['agent_profit']=i.agent_profit
        profit_dic['issue']=i.issue
        profit_dic['create_time']=str(i.create_time)
        lists.append(profit_dic)

    return HttpResponse(ujson.dumps(lists))











@csrf_exempt
@login_required(login_url="/xadmin/")
def my_gather(request):
    #print '-----------------1111'
    ret = []
    _total=0
    us_name = request.user.username

    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码

    if(_pnum<1):
        _pnum=1
    _start_index=(_pnum-1)*_psize#从此节点获取数据
    _start_ii=0
    #print '-------------------------0'
    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    # 开始日期小于结束日期
    num_0 = int(_date_0.replace('-', ''))
    num_1 = int(_date_1.replace('-', ''))
    if (num_0 <= num_1):
        if act==0:#安年
            #print '------------------------1'
            year_start = int(_date_0)  # .split('-')[0])
            year_end = int(_date_1)  # .split('-')[0])
            _total = year_end - year_start + 1
            year_cur = year_end
            # print(year_start,'---year---',year_end)
            while year_start <= year_cur:
                #print '---------------------2'
                if (_start_ii - _start_index) >= _psize:  # 够一页了
                    break
                tmp_0 = str(year_cur) + '-01-01'
                tmp_1 = str(year_cur) + '-12-31'
                if _start_index <= _start_ii:
                    #print '-------------ssssss'
                    db_data = AgentTableReport.objects.filter(create_date__range=(tmp_0, tmp_1),agent__account=us_name). \
                        aggregate(time_15=Sum('time_15'), time_20=Sum('time_20'),time_30=Sum('time_30'),
                                    time_60=Sum('time_60'),allmoney=Sum('allmoney'))
                    #print year_cur,'------------',db_data
                    cur_obj = {'date': year_cur, 'time_15': 0, 'time_20': 0,'time_30': 0, 'time_60': 0,'allmoney': 0, 'total_number': 0}
                    if db_data['allmoney']:
                        cur_obj = {'date': year_cur, 'time_15': db_data['time_15'], 'time_20':db_data['time_20'] ,
                               'time_30':db_data['time_30'] ,'time_60':db_data['time_60'] ,'allmoney': db_data['allmoney'] ,\
                               'total_number':0 }
                        cur_obj['total_number']=db_data['time_15']+db_data['time_20']+db_data['time_30']+db_data['time_60']
                    ret.append(cur_obj)
                year_cur-=1
                _start_ii+=1
        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            #计算月份数
            if int(year_end)>int(year_start):
                _total=12-int(month_start)+1
                _total+=(int(year_end)-int(year_start))*12
                _total+=int(month_end)
            else:
                _total=int(month_end)-int(month_start)+1

            num_cur=num_1
            year_cur=year_end
            month_cur=month_end
            while num_0<=num_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=year_cur+'-'+month_cur+'-01'
                #下个月1号,减少一天
                cur_time=datetime.datetime.strptime(tmp_0, "%Y-%m-%d")
                cur_time=fun.datetime_offset_by_month(cur_time)
                delta=datetime.timedelta(days=-1)
                time_next=cur_time+delta
                tmp_1=time_next.strftime('%Y-%m-%d')
                #print tmp_0,'--------------------',tmp_1
                if _start_index<=_start_ii :
                    db_data = AgentTableReport.objects.filter(create_date__gte=tmp_0, create_date__lt=tmp_1,
                                                              agent__account=us_name). \
                        aggregate(time_15=Sum('time_15'), time_20=Sum('time_20'), time_30=Sum('time_30'),
                                  time_60=Sum('time_60'), allmoney=Sum('allmoney'))
                    #print year_cur, '------------', db_data
                    cur_obj = {'date': year_cur+"-"+month_cur, 'time_15': 0, 'time_20': 0, 'time_30': 0, 'time_60': 0, 'allmoney': 0,
                               'total_number': 0}
                    if db_data['allmoney']:
                        cur_obj = {'date': year_cur+"-"+month_cur, 'time_15': db_data['time_15'], 'time_20': db_data['time_20'],
                                   'time_30': db_data['time_30'], 'time_60': db_data['time_60'],
                                   'allmoney': db_data['allmoney'],'total_number': 0}
                        cur_obj['total_number'] = db_data['time_15'] + db_data['time_20'] + db_data['time_30'] + \
                                                  db_data['time_60']
                    ret.append(cur_obj)
                if int(month_cur)==1:
                    month_cur="12"
                    year_cur=str(int(year_cur)-1)
                else:
                    tmp_val=int(month_cur)-1
                    if(tmp_val<10):
                        tmp_val="0"+str(tmp_val)
                    month_cur=str(tmp_val)

                num_cur=int(year_cur+month_cur)
                _start_ii+=1
        elif act==2:#日期
            _y_0,_m_0,_d_0=_date_0.split('-')
            _y_1,_m_1,_d_1=_date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0),int(_d_0))
            d1 = datetime.datetime(int(_y_1),int( _m_1),int(_d_1))
            _total=(d1-d0).days+1
            d_cur=d1+datetime.timedelta(-_start_index)
            if(_total<_psize):
                _psize=_total
            while(_start_ii<_psize and d_cur>=d0):
                tmp_0=d_cur.strftime('%Y-%m-%d')
                d_next=d_cur+datetime.timedelta(+1)
                tmp_1=d_next.strftime('%Y-%m-%d')

                db_data = AgentTableReport.objects.filter(create_date__gte=tmp_0, create_date__lt=tmp_1,
                                                          agent__account=us_name). \
                    aggregate(time_15=Sum('time_15'), time_20=Sum('time_20'), time_30=Sum('time_30'),
                              time_60=Sum('time_60'), allmoney=Sum('allmoney'))
                # print year_cur, '------------', db_data
                cur_obj = {'date': tmp_0, 'time_15': 0, 'time_20': 0, 'time_30': 0, 'time_60': 0,
                           'allmoney': 0,
                           'total_number': 0}
                if db_data['allmoney']:
                    cur_obj = {'date': tmp_0, 'time_15': db_data['time_15'],
                               'time_20': db_data['time_20'],
                               'time_30': db_data['time_30'], 'time_60': db_data['time_60'],
                               'allmoney': db_data['allmoney'], 'total_number': 0}
                    cur_obj['total_number'] = db_data['time_15'] + db_data['time_20'] + db_data['time_30'] + \
                                              db_data['time_60']
                ret.append(cur_obj)
                #上一天
                _start_ii+=1
                d_cur=d_cur+datetime.timedelta(-1)
    ret = {'total': _total, 'rows': ret}
    return HttpResponse(ujson.dumps(ret))



@csrf_exempt
@login_required(login_url="/xadmin/")
def admin_gather(request):
    ret = []
    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码
    username = request.GET['name']

    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    # 开始日期小于结束日期
    #print _date_0,_date_1
    num_0 = int(_date_0.replace('-', ''))
    num_1 = int(_date_1.replace('-', ''))
    if (num_0 <= num_1):
        # 按年2016  2017
        if act == 0:
            year_start = int(_date_0)  # .split('-')[0])
            year_end = int(_date_1)  # .split('-')[0])
            # _total = year_end - year_start + 1
            year_cur = year_end
            tmp_0 = str(year_start) + '-01-01'
            tmp_1 = str(year_cur) + '-12-31'
            cur_filter=Agent_Profit.objects.filter(date__range=(tmp_0,tmp_1))
            if username!='':
                agentid=AgentInfo.objects.get(account=username).id
                cur_filter=cur_filter.filter(agentid_id=agentid)
            data=cur_filter.values('account').annotate(profits=Sum('profit'))
            for i in data:
                cur_obj = {'account': '', 'profit': 0,'date':''}
                cur_obj['account']=i['account']
                cur_obj['profit']=i['profits']
                cur_obj['date']=str(tmp_0) + ','+str(tmp_1)
                ret.append(cur_obj)
        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            num_cur=num_1
            year_cur=year_start
            tmp_0=year_cur+'-'+month_start+'-01'
            temps = year_end+'-'+month_end+'-01'
            #下个月1号,减少一天
            cur_time=datetime.datetime.strptime(temps, "%Y-%m-%d")
            cur_time=fun.datetime_offset_by_month(cur_time)
            delta=datetime.timedelta(days=-1)
            time_next=cur_time+delta
            tmp_1=time_next.strftime('%Y-%m-%d')
            cur_filter=Agent_Profit.objects.filter(date__range=(tmp_0,tmp_1))
            if username!='':
                agentid=AgentInfo.objects.get(account=username).id
                cur_filter=cur_filter.filter(agentid_id=agentid)
            data=cur_filter.values('account').annotate(profits=Sum('profit'))
            for i in data:
                cur_obj = {'account': '', 'profit': 0,'date':''}
                cur_obj['account']=i['account']
                cur_obj['profit']=i['profits']
                cur_obj['date']=str(tmp_0) + ','+str(tmp_1)
                ret.append(cur_obj)
        elif act == 2:  # 日期
            _y_0, _m_0, _d_0 = _date_0.split('-')
            _y_1, _m_1, _d_1 = _date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0), int(_d_0))
            d1 = datetime.datetime(int(_y_1), int(_m_1), int(_d_1))
            # _total = (d1 - d0).days + 1
            # d_cur = d1 + datetime.timedelta(-_start_index)
            # tmp_0 = d_cur.strftime('%Y-%m-%d')
            # d_next = d_cur + datetime.timedelta(+1)
            # tmp_1 = d_next.strftime('%Y-%m-%d')
            cur_filter=Agent_Profit.objects.filter(date__range=(d0,d1))
            if username!='':
                agentid=AgentInfo.objects.get(account=username).id
                cur_filter=cur_filter.filter(agentid_id=agentid)
            data=cur_filter.values('account').annotate(profits=Sum('profit'))
            for i in data:
                cur_obj = {'account': '', 'profit': 0,'date':''}
                cur_obj['account']=i['account']
                cur_obj['profit']=i['profits']
                cur_obj['date']=str(d0).split(' ')[0] + ','+str(d1).split(' ')[0]
                ret.append(cur_obj)
    #ret = {'total':_total,'rows': ret}
    return HttpResponse(ujson.dumps(ret))


@csrf_exempt
@login_required(login_url="/xadmin/")
def op_recharge(request):
    ret = []
    _total=0

    _pf_id=request.GET['pf']
    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码
    if(_pnum<1):
        _pnum=1
    _start_index=(_pnum-1)*_psize#从此节点获取数据
    _start_ii=0

    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    #开始日期小于结束日期
    num_0=int(_date_0.replace('-',''))
    num_1=int(_date_1.replace('-',''))
    if(num_0<=num_1):
        #按年2016  2017
        if act==0:
            year_start=int(_date_0)#.split('-')[0])
            year_end=int(_date_1)#.split('-')[0])
            _total=year_end-year_start+1
            year_cur=year_end
            #print(year_start,'---year---',year_end)
            while year_start<=year_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=str(year_cur)+'-01-01'
                tmp_1=str(year_cur)+'-12-31'
                if _start_index<=_start_ii:
                    #db_data=CommissionReport.objects.filter(pay_date__range=(tmp_0,tmp_1),account='',classid='').aggregate(money=Sum("money"),com=Sum("commission"))
                    cur_filter=CommissionReport.objects.filter(pay_date__range=(tmp_0,tmp_1),account='',classid='')
                    if _pf_id!='':
                        cur_filter=cur_filter.filter(appid=_pf_id)
                    db_data=cur_filter.aggregate(money=Sum("money"),com=Sum("commission"))

                    cur_obj={'date':year_cur,'money':db_data['money'] or 0,'com':db_data['com'] or 0}
                    ret.append(cur_obj)
                year_cur-=1
                _start_ii+=1
        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            #计算月份数
            if int(year_end)>int(year_start):
                _total=12-int(month_start)+1
                _total+=(int(year_end)-int(year_start))*12
                _total+=int(month_end)
            else:
                _total=int(month_end)-int(month_start)+1

            num_cur=num_1
            year_cur=year_end
            month_cur=month_end
            while num_0<=num_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=year_cur+'-'+month_cur+'-01'
                #下个月1号,减少一天
                cur_time=datetime.datetime.strptime(tmp_0, "%Y-%m-%d")
                cur_time=fun.datetime_offset_by_month(cur_time)
                delta=datetime.timedelta(days=-1)
                time_next=cur_time+delta
                tmp_1=time_next.strftime('%Y-%m-%d')
                #print tmp_0,'--------------------',tmp_1,_pf_id
                if _start_index<=_start_ii :
                    #db_data=CommissionReport.objects.filter(pay_date__gte=tmp_0 ,pay_date__lt=tmp_1,account='',classid='').aggregate(money=Sum("money"),com=Sum("commission"))
                    cur_filter=CommissionReport.objects.filter(pay_date__gte=tmp_0 ,pay_date__lt=tmp_1,account='',classid='')
                    if _pf_id!='':
                        cur_filter=cur_filter.filter(appid=_pf_id)
                    db_data=cur_filter.aggregate(money=Sum("money"),com=Sum("commission"))
                    cur_obj={'date':year_cur+"-"+month_cur,'money':db_data['money'] or 0,'com':db_data['com'] or 0}
                    ret.append(cur_obj)
                #前1月
                if int(month_cur)==1:
                    month_cur="12"
                    year_cur=str(int(year_cur)-1)
                else:
                    tmp_val=int(month_cur)-1
                    if(tmp_val<10):
                        tmp_val="0"+str(tmp_val)
                    month_cur=str(tmp_val)

                num_cur=int(year_cur+month_cur)
                _start_ii+=1
        elif act==2:#日期
            _y_0,_m_0,_d_0=_date_0.split('-')
            _y_1,_m_1,_d_1=_date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0),int(_d_0))
            d1 = datetime.datetime(int(_y_1),int( _m_1),int(_d_1))
            _total=(d1-d0).days+1
            d_cur=d1+datetime.timedelta(-_start_index)
            if(_total<_psize):
                _psize=_total
            while(_start_ii<_psize and d_cur>=d0):
                tmp_0=d_cur.strftime('%Y-%m-%d');
                d_next=d_cur+datetime.timedelta(+1)
                tmp_1=d_next.strftime('%Y-%m-%d');

                #db_data=CommissionReport.objects.filter(pay_date__gte=tmp_0 ,pay_date__lt=tmp_1,account='',classid='').aggregate(money=Sum("money"),com=Sum("commission"))
                cur_filter=CommissionReport.objects.filter(pay_date__gte=tmp_0 ,pay_date__lt=tmp_1,account='',classid='')
                if _pf_id!='':
                    cur_filter=cur_filter.filter(appid=_pf_id)
                db_data=cur_filter.aggregate(money=Sum("money"),com=Sum("commission"))
                cur_obj={'date':tmp_0,'money':db_data['money'] or 0,'com':db_data['com'] or 0}
                ret.append(cur_obj)
                #上一天
                _start_ii+=1
                d_cur=d_cur+datetime.timedelta(-1)

    ret={'total':_total,'rows':ret}
    return HttpResponse(ujson.dumps(ret))

def my_custom_sql(sql):
    from django.db import connection, transaction
    cursor = connection.cursor()

    # 数据修改操作——提交要求
    # cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
    # transaction.commit_unless_managed()

    # 数据检索操作,不需要提交
    #cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
    #row = cursor.fetchone()
    print '--my_custom_sql-->',sql
    cursor.execute(sql)
    row = cursor.fetchall()
    #print sql,'---my_custom_sql----',row
    return row

#判断是否windows
import platform
def is_windows():
    cur_sys=platform.system().lower()
    return cur_sys.find('windows')>-1


@csrf_exempt
@login_required(login_url="/xadmin/")
def op_consume(request):
    ret = []
    _total=0

    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码
    if(_pnum<1):
        _pnum=1
    _start_index=(_pnum-1)*_psize#从此节点获取数据
    _start_ii=0

    #获取数据库的数据
    db_data=[]
    def get_db_data(_dateFormat,_sdate,_edate):
        if not is_windows():
            _dateFormat=_dateFormat.replace('%%','%')#linux
        t_sql="SELECT SUM(consumepoint) AS upoint,DATE_FORMAT(create_time,'{0}') AS pdate FROM websys_userconsumerecord WHERE consumepoint<0 and create_time>='{1}' and create_time<='{2}'"
        if _name!='':
            t_sql+=" and uid_id=(select id from websys_userinfo where nickname like '%"+_name+"%')"
        t_sql+=" GROUP BY DATE_FORMAT(create_time,'{0}')"
        # t_sql="SELECT COUNT(DISTINCT uid_id) AS ucount,SUM(consumepoint) AS upoint,'2017-07-01' AS pdate FROM websys_userconsumerecord " \
        #                                                                                                        "WHERE typeid=1 and create_time>='{1}' and create_time<='{2}' GROUP BY '2017-07-01'"
        t_sql=t_sql.format(_dateFormat,_sdate,_edate)
        t_row=my_custom_sql(t_sql)
        t_res={}
        for ii in t_row:
            t_res[ii[1]]={'upoint':ii[0]}
        #print t_res,'--get_db_data---'
        return  t_res
        # if(_name==''):
        #     t_sql=t_sql.format(_dateFormat,)
        #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0).aggregate(money=Sum("consumepoint"),com=Sum("id"))
        # else:
        #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0,uid__nickname__contains=_name).aggregate(money=Sum("consumepoint"),com=Sum("id"))

    #读取数据库的,重新设置
    def reset_data_fromdb():
            max_date=ret[0]['date']
            min_date=ret[len(ret)-1]['date']
            if act==0:
                db_res=get_db_data('%%Y',min_date+'-01-01',max_date+'-12-31')
            elif act==1:
                db_res=get_db_data('%%Y-%%m',min_date+'-01',max_date+'-31 23:59:59')
            elif act==2:
                db_res=get_db_data('%%Y-%%m-%%d',min_date,max_date+' 23:59:59')
            #print ret,'--reset_data_fromdb---'
            for ii_item in ret:
                ii_date=ii_item['date']
                if ii_date in db_res:
                    db_data=db_res[ii_date]
                    ii_item['money']=db_data['upoint']
                    #ii_item['num']=db_data['ucount']
            #print ret

    _name = request.GET["name"]
    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    #开始日期小于结束日期
    num_0=int(_date_0.replace('-',''))
    num_1=int(_date_1.replace('-',''))
    if(num_0<=num_1):
        #按年2016  2017
        if act==0:
            year_start=int(_date_0)#.split('-')[0])
            year_end=int(_date_1)#.split('-')[0])
            _total=year_end-year_start+1
            year_cur=year_end
            #print(year_start,'---year---',year_end)
            while year_start<=year_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=str(year_cur)+'-01-01'
                tmp_1=str(year_cur+1)+'-01-31'
                if _start_index<=_start_ii:
                    # if(_name==''):
                    #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0).aggregate(money=Sum("consumepoint"),com=Sum("id"))
                    # else:
                    #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0,uid__nickname__contains=_name).aggregate(money=Sum("consumepoint"),com=Sum("id"))
                    #
                    # cur_obj={'date':year_cur,'money':db_data['money'] or 0,'com':db_data['com'] or 0}
                    cur_obj={'date':str(year_cur),'money':0,'com':0}
                    ret.append(cur_obj)
                year_cur-=1
                _start_ii+=1

            reset_data_fromdb()

        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            #计算月份数
            if int(year_end)>int(year_start):
                _total=12-int(month_start)+1
                _total+=(int(year_end)-int(year_start))*12
                _total+=int(month_end)
            else:
                _total=int(month_end)-int(month_start)+1

            num_cur=num_1
            year_cur=year_end
            month_cur=month_end
            while num_0<=num_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=year_cur+'-'+month_cur+'-01'
                #下个月1号,减少一天
                cur_time=datetime.datetime.strptime(tmp_0, "%Y-%m-%d")
                cur_time=fun.datetime_offset_by_month(cur_time)
                delta=datetime.timedelta(days=-1)
                time_next=cur_time+delta
                tmp_1=time_next.strftime('%Y-%m-%d')
                #print tmp_0,'--------------------',tmp_1
                if _start_index<=_start_ii :
                    # if(_name==''):
                    #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0).aggregate(money=Sum("consumepoint"),com=Sum("id"))
                    # else:
                    #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0,uid__nickname__contains=_name).aggregate(money=Sum("consumepoint"),com=Sum("id"))
                    # cur_obj={'date':year_cur+"-"+month_cur,'money':db_data['money'] or 0,'com':db_data['com'] or 0}
                    cur_obj={'date':year_cur+"-"+month_cur,'money':0,'com':0}
                    ret.append(cur_obj)
                #前1月
                if int(month_cur)==1:
                    month_cur="12"
                    year_cur=str(int(year_cur)-1)
                else:
                    tmp_val=int(month_cur)-1
                    if(tmp_val<10):
                        tmp_val="0"+str(tmp_val)
                    month_cur=str(tmp_val)

                num_cur=int(year_cur+month_cur)
                _start_ii+=1

            reset_data_fromdb()
        elif act==2:#日期
            _y_0,_m_0,_d_0=_date_0.split('-')
            _y_1,_m_1,_d_1=_date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0),int(_d_0))
            d1 = datetime.datetime(int(_y_1),int( _m_1),int(_d_1))
            _total=(d1-d0).days+1
            d_cur=d1+datetime.timedelta(-_start_index)
            if(_total<_psize):
                _psize=_total
            while(_start_ii<_psize and d_cur>=d0):
                tmp_0=d_cur.strftime('%Y-%m-%d');
                d_next=d_cur+datetime.timedelta(+1)
                tmp_1=d_next.strftime('%Y-%m-%d');
                # if(_name==''):
                #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0).aggregate(money=Sum("consumepoint"),com=Sum("id"))
                # else:
                #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0,uid__nickname__contains=_name).aggregate(money=Sum("consumepoint"),com=Sum("id"))
                # cur_obj={'date':tmp_0,'money':db_data['money'] or 0,'com':db_data['com'] or 0}
                cur_obj={'date':tmp_0,'money': 0,'com': 0}
                ret.append(cur_obj)
                #上一天
                _start_ii+=1
                d_cur=d_cur+datetime.timedelta(-1)

            reset_data_fromdb()

    ret={'total':_total,'rows':ret}
    return HttpResponse(ujson.dumps(ret))

####################分享数据#################################
@csrf_exempt
@login_required(login_url="/xadmin/")
def op_share(request):
    ret = []
    _total=0

    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码
    if(_pnum<1):
        _pnum=1
    _start_index=(_pnum-1)*_psize#从此节点获取数据
    _start_ii=0

    #获取数据库的数据
    db_data=[]
    def get_db_data(_dateFormat,_sdate,_edate):
        if not is_windows():
            _dateFormat=_dateFormat.replace('%%','%')#linux
        t_sql="SELECT COUNT(DISTINCT uid_id) AS ucount,SUM(consumepoint) AS upoint,DATE_FORMAT(create_time,'{0}') AS pdate FROM websys_userconsumerecord " \
                                                                                                               "WHERE typeid=21 and create_time>='{1}' and create_time<='{2}' GROUP BY DATE_FORMAT(create_time,'{0}')"
        # t_sql="SELECT COUNT(DISTINCT uid_id) AS ucount,SUM(consumepoint) AS upoint,'2017-07-01' AS pdate FROM websys_userconsumerecord " \
        #                                                                                                        "WHERE typeid=1 and create_time>='{1}' and create_time<='{2}' GROUP BY '2017-07-01'"
        t_sql=t_sql.format(_dateFormat,_sdate,_edate)
        t_row=my_custom_sql(t_sql)
        t_res={}
        for ii in t_row:
            t_res[ii[2]]={'ucount':ii[0],'upoint':ii[1]}
        #print t_res,'--get_db_data---'
        return  t_res
        # if(_name==''):
        #     t_sql=t_sql.format(_dateFormat,)
        #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0).aggregate(money=Sum("consumepoint"),com=Sum("id"))
        # else:
        #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0,uid__nickname__contains=_name).aggregate(money=Sum("consumepoint"),com=Sum("id"))

    #读取数据库的,重新设置
    def reset_data_fromdb():
            max_date=ret[0]['date']
            min_date=ret[len(ret)-1]['date']
            if act==0:
                db_res=get_db_data('%%Y',min_date+'-01-01',max_date+'-12-31')
            elif act==1:
                db_res=get_db_data('%%Y-%%m',min_date+'-01',max_date+'-31 23:59:59')
            elif act==2:
                db_res=get_db_data('%%Y-%%m-%%d',min_date,max_date+' 23:59:59')
            #print ret,'--reset_data_fromdb---'
            for ii_item in ret:
                ii_date=ii_item['date']
                if ii_date in db_res:
                    db_data=db_res[ii_date]
                    ii_item['money']=db_data['upoint']
                    ii_item['num']=db_data['ucount']
            #print ret

    _name = request.GET["name"]
    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    #开始日期小于结束日期
    num_0=int(_date_0.replace('-',''))
    num_1=int(_date_1.replace('-',''))
    if(num_0<=num_1):
        #按年2016  2017
        if act==0:
            year_start=int(_date_0)#.split('-')[0])
            year_end=int(_date_1)#.split('-')[0])
            _total=year_end-year_start+1
            year_cur=year_end
            #print(year_start,'---year---',year_end)

            while year_start<=year_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=str(year_cur)+'-01-01'
                tmp_1=str(year_cur+1)+'-01-31'
                if _start_index<=_start_ii:
                    cur_obj={'date':str(year_cur),'money':0,'num':0}
                    ret.append(cur_obj)
                year_cur-=1
                _start_ii+=1

            reset_data_fromdb()

        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            #计算月份数
            if int(year_end)>int(year_start):
                _total=12-int(month_start)+1
                _total+=(int(year_end)-int(year_start))*12
                _total+=int(month_end)
            else:
                _total=int(month_end)-int(month_start)+1

            num_cur=num_1
            year_cur=year_end
            month_cur=month_end
            while num_0<=num_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=year_cur+'-'+month_cur+'-01'
                #下个月1号,减少一天
                cur_time=datetime.datetime.strptime(tmp_0, "%Y-%m-%d")
                cur_time=fun.datetime_offset_by_month(cur_time)
                delta=datetime.timedelta(days=-1)
                time_next=cur_time+delta
                tmp_1=time_next.strftime('%Y-%m-%d')
                #print tmp_0,'--------------------',tmp_1
                if _start_index<=_start_ii :
                    cur_obj={'date':year_cur+"-"+month_cur,'money': 0,'num':0}
                    ret.append(cur_obj)
                #前1月
                if int(month_cur)==1:
                    month_cur="12"
                    year_cur=str(int(year_cur)-1)
                else:
                    tmp_val=int(month_cur)-1
                    if(tmp_val<10):
                        tmp_val="0"+str(tmp_val)
                    month_cur=str(tmp_val)

                num_cur=int(year_cur+month_cur)
                _start_ii+=1

            #读取数据库的
            reset_data_fromdb()

        elif act==2:#日期
            _y_0,_m_0,_d_0=_date_0.split('-')
            _y_1,_m_1,_d_1=_date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0),int(_d_0))
            d1 = datetime.datetime(int(_y_1),int( _m_1),int(_d_1))
            _total=(d1-d0).days+1
            d_cur=d1+datetime.timedelta(-_start_index)
            if(_total<_psize):
                _psize=_total

            while(_start_ii<_psize and d_cur>=d0):
                tmp_0=d_cur.strftime('%Y-%m-%d');
                d_next=d_cur+datetime.timedelta(+1)
                tmp_1=d_next.strftime('%Y-%m-%d');

                cur_obj={'date':tmp_0,'money':0,'num':0}
                ret.append(cur_obj)
                #上一天
                _start_ii+=1
                d_cur=d_cur+datetime.timedelta(-1)

            reset_data_fromdb()

    ret={'total':_total,'rows':ret}
    return HttpResponse(ujson.dumps(ret))

####################开房数据#################################
@csrf_exempt
@login_required(login_url="/xadmin/")
def op_table(request):
    ret = []
    _total=0

    _psize=int(request.GET["pageSize"])#页记录数
    _pnum=int(request.GET["pageNumber"])#当前页码
    if(_pnum<1):
        _pnum=1
    _start_index=(_pnum-1)*_psize#从此节点获取数据
    _start_ii=0

    #获取数据库的数据
    db_data=[]
    def get_db_data(_dateFormat,_sdate,_edate,_games,_isdate=0):
        if not is_windows():
            _dateFormat=_dateFormat.replace('%%','%')#linux
        t_sql="SELECT sum(ucount) AS ucount,SUM(tcount) AS tcount,DATE_FORMAT(tdate,'{0}') AS pdate FROM websys_tablereport WHERE tdate>='{1}' and tdate<='{2}'"
        if _games!='':
            t_sql+=" and gameid in ("+_games+")"
        t_sql+=" GROUP BY DATE_FORMAT(tdate,'{0}')"
        #t_sql="SELECT sum(ucount) AS ucount,SUM(tcount) AS tcount,'2017-08-28' as pdate from websys_tablereport"
        # if _isdate:#按天汇总的
        #     t_sql="select ucount,tcount,DATE_FORMAT(tdate,'{0}') AS pdate from websys_tablereport WHERE tdate>='{1}' and tdate<='{2}'"

        # t_sql="SELECT COUNT(DISTINCT uid_id) AS ucount,SUM(consumepoint) AS upoint,'2017-07-01' AS pdate FROM websys_userconsumerecord " \
        #                                                                                                        "WHERE typeid=1 and create_time>='{1}' and create_time<='{2}' GROUP BY '2017-07-01'"
        t_sql=t_sql.format(_dateFormat,_sdate,_edate)
        t_row=my_custom_sql(t_sql)
        t_res={}
        for ii in t_row:
            t_res[ii[2]]={'ucount':ii[0],'upoint':ii[1]}
        #print t_res,'--get_db_data---'
        return  t_res
        # if(_name==''):
        #     t_sql=t_sql.format(_dateFormat,)
        #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0).aggregate(money=Sum("consumepoint"),com=Sum("id"))
        # else:
        #     db_data=UserConsumeRecord.objects.filter(create_time__gte=tmp_0 ,create_time__lt=tmp_1,consumepoint__lt=0,uid__nickname__contains=_name).aggregate(money=Sum("consumepoint"),com=Sum("id"))

    #读取数据库的,重新设置
    def reset_data_fromdb():
            max_date=ret[0]['date']
            min_date=ret[len(ret)-1]['date']
            if act==0:
                db_res=get_db_data('%%Y',min_date+'-01-01',max_date+'-12-31',_game_ids)
            elif act==1:
                db_res=get_db_data('%%Y-%%m',min_date+'-01',max_date+'-31',_game_ids)
            elif act==2:
                db_res=get_db_data('%%Y-%%m-%%d',min_date,max_date,_game_ids,1)
            #print ret,'--reset_data_fromdb---'
            for ii_item in ret:
                ii_date=ii_item['date']
                if ii_date in db_res:
                    db_data=db_res[ii_date]
                    ii_item['money']=db_data['upoint']
                    ii_item['num']=db_data['ucount']
            #print ret

    _pf = request.GET["pf"]
    _game_ids=getPF_GAMES(_pf)
    _name = request.GET["name"]
    _date = request.GET["date"]
    act = int(request.GET["type"])
    _date_0,_date_1=_date.split(',')
    #开始日期小于结束日期
    num_0=int(_date_0.replace('-',''))
    num_1=int(_date_1.replace('-',''))
    if(num_0<=num_1):
        #按年2016  2017
        if act==0:
            year_start=int(_date_0)#.split('-')[0])
            year_end=int(_date_1)#.split('-')[0])
            _total=year_end-year_start+1
            year_cur=year_end
            #print(year_start,'---year---',year_end)

            while year_start<=year_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=str(year_cur)+'-01-01'
                tmp_1=str(year_cur+1)+'-01-31'
                if _start_index<=_start_ii:
                    cur_obj={'date':str(year_cur),'money':0,'num':0}
                    ret.append(cur_obj)
                year_cur-=1
                _start_ii+=1

            reset_data_fromdb()

        elif act==1:#月份
            year_start,month_start=_date_0.split('-')
            year_end,month_end=_date_1.split('-')

            #计算月份数
            if int(year_end)>int(year_start):
                _total=12-int(month_start)+1
                _total+=(int(year_end)-int(year_start))*12
                _total+=int(month_end)
            else:
                _total=int(month_end)-int(month_start)+1

            num_cur=num_1
            year_cur=year_end
            month_cur=month_end
            while num_0<=num_cur:
                if (_start_ii-_start_index)>=_psize:#够一页了
                    break
                tmp_0=year_cur+'-'+month_cur+'-01'
                #下个月1号,减少一天
                cur_time=datetime.datetime.strptime(tmp_0, "%Y-%m-%d")
                cur_time=fun.datetime_offset_by_month(cur_time)
                delta=datetime.timedelta(days=-1)
                time_next=cur_time+delta
                tmp_1=time_next.strftime('%Y-%m-%d')
                #print tmp_0,'--------------------',tmp_1
                if _start_index<=_start_ii :
                    cur_obj={'date':year_cur+"-"+month_cur,'money': 0,'num':0}
                    ret.append(cur_obj)
                #前1月
                if int(month_cur)==1:
                    month_cur="12"
                    year_cur=str(int(year_cur)-1)
                else:
                    tmp_val=int(month_cur)-1
                    if(tmp_val<10):
                        tmp_val="0"+str(tmp_val)
                    month_cur=str(tmp_val)

                num_cur=int(year_cur+month_cur)
                _start_ii+=1

            #读取数据库的
            reset_data_fromdb()

        elif act==2:#日期
            _y_0,_m_0,_d_0=_date_0.split('-')
            _y_1,_m_1,_d_1=_date_1.split('-')
            d0 = datetime.datetime(int(_y_0), int(_m_0),int(_d_0))
            d1 = datetime.datetime(int(_y_1),int( _m_1),int(_d_1))
            _total=(d1-d0).days+1
            d_cur=d1+datetime.timedelta(-_start_index)
            if(_total<_psize):
                _psize=_total

            while(_start_ii<_psize and d_cur>=d0):
                tmp_0=d_cur.strftime('%Y-%m-%d');
                d_next=d_cur+datetime.timedelta(+1)
                tmp_1=d_next.strftime('%Y-%m-%d');

                cur_obj={'date':tmp_0,'money':0,'num':0}
                ret.append(cur_obj)
                #上一天
                _start_ii+=1
                d_cur=d_cur+datetime.timedelta(-1)

            reset_data_fromdb()

    ret={'total':_total,'rows':ret}
    return HttpResponse(ujson.dumps(ret))

################在线相关###########################
@csrf_exempt
@login_required(login_url="/xadmin/")
def op_online(request):
    ret = {"ret": 0, "data": {}}
    _id = request.GET["id"]#0大厅，其它为游戏

    try:
        rc = db_redis.get_redis()
        hall_arr=rc.keys('hall_online_*')
        game_arr=rc.keys('game_online_*')
        for ii in game_arr:
            tmp_key=ii.replace('game','hall')
            if tmp_key in hall_arr:
                hall_arr.remove(tmp_key)

        ret['data']['hall']=len(hall_arr)
        ret['data']['game']=len(game_arr)
    except:
        ret['data']['hall']=-1
        ret['data']['game']=-1

    return HttpResponse(ujson.dumps(ret))

GAME_OBJ={}
@csrf_exempt
@login_required(login_url="/xadmin/")
def op_onlinecount(request):
    ret = {"ret": 0, "data": {}}
    #_id = request.GET["id"]#0大厅，其它为游戏

    try:
        global  GAME_OBJ
        if len(GAME_OBJ)==0:#首次读取数据库
            gis=GameInfo.objects.all().values('id','gamename')
            for ii in gis:
                GAME_OBJ[str(ii['id'])]=[]
        else:
            for id in GAME_OBJ:
                GAME_OBJ[id]=[]
        GAME_OBJ['0']=[]

        rc = db_redis.get_redis()
        hall_arr=rc.keys('hall_online_*')
        game_arr=rc.keys('game_online_*')
        #print(GAME_OBJ,'hall_arr',hall_arr,'game_arr',game_arr)
        for ii in game_arr:
            tmp_key=ii.replace('game','hall')
            if tmp_key in hall_arr:
                hall_arr.remove(tmp_key)
            ii_uid= ii.split('_')[-1]

            ii_data=rc.get(ii)#1_123456 游戏_房间号
            if not ii_data:
                print ii,'--op_onlinecount--redis err-',
                continue
            if(ii_data.find('_')>0):
                ii_gid,ii_rid=ii_data.split('_')
                ii_uid+='_'+ii_rid
            else:
                ii_gid=ii_data
            GAME_OBJ[str(ii_gid)].append(ii_uid)
        #print(GAME_OBJ)
        tmp_len=len(hall_arr)
        GAME_OBJ['0']=map(lambda x:x.replace('hall_online_',''),hall_arr)
        ret['data']['0']=tmp_len
        for id in GAME_OBJ:
            ret['data'][id]=len(GAME_OBJ[id])
        #print(ret)
    except Exception, e:
        print(traceback.format_exc())
        ret['data']['0']=-1
        ret['data']['1']=-1

    return HttpResponse(ujson.dumps(ret))

@csrf_exempt
@login_required(login_url="/xadmin/")
def op_onlinelist(request):
    ret = []
    _id = request.GET["id"]#0大厅，其它为游戏

    global  GAME_OBJ
    #print(GAME_OBJ,'op_onlinelist',_id)
    if len(GAME_OBJ)>0 and (_id in GAME_OBJ ):
    #if True:
        try:
            uid_obj={}
            uid_arr=[]
            data_arr=GAME_OBJ[_id]
            for item in data_arr:
                if(item.find('_')>0):
                    _id,_rid=item.split('_')
                    uid_arr.append(_id)
                    uid_obj[_id]=_rid
                else:
                    uid_arr.append(item)
            #game_online=11

            #uid_arr=GAME_OBJ[_id]
            #uid_arr=['9', '24', '12', '29', '22', '17', '26', '10', '11', '12', '14', '23', '11', '24', '23', '28']
            if len(uid_arr)>0:
                #uid_arr=map(lambda x:int(x),uid_arr)
                #uid_arr=[973,2402,1276,2491,2352,1227,2678,1470,1157,1242,1409,2386,1501,2474,2397,2398]

                u_data=UserInfo.objects.filter(id__in=uid_arr)#.values("id","nickname","head_img","gamepoint")
                #print(u_data)
                #[{"id":222,"nickname":2333,"head_img":3333,"gamepoint"},{}]
                #ret=u_data
                for item in u_data:
                    tmp_obj={"id":item.pk,"nickname":item.nickname,"head_img":item.head_img,"gamepoint":item.gamepoint,"roomid":""}
                    tmp_uid=str(tmp_obj['id'])
                    if tmp_uid in uid_obj:
                        tmp_obj['roomid']=uid_obj[tmp_uid]
                    ret.append(tmp_obj)
                ret.sort(key = lambda x:x["gamepoint"])
        except:
            pass

    return HttpResponse(ujson.dumps(ret))